package com.fuyq.spring04.jdbc;

import com.fuyq.spring04.beans.order.Order;
import org.springframework.stereotype.Component;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@Component
public class OrderService {
    private static final Connection conn = MysqlConn.getConnection();

    //增加
    public void insert(Order order) throws SQLException {
        String sql = "INSERT INTO order(id,order_no) values(" + "?,?)";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, order.getId());
        pstmt.setString(2, order.getOrderNo());
        pstmt.execute();
    }

    //修改
    public void update(Order order) throws SQLException {
        String sql = "UPDATE order set order_no=?  where id=?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, order.getOrderNo());
        pstmt.setString(2, order.getId());
        pstmt.execute();
    }

    //删除
    public void delete(String id) throws SQLException {
        String sql = "delete from order where id=?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, id);
        pstmt.execute();
    }

    //查询
    public List<Order> query() throws SQLException {
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT user_name, age FROM imooc_goddess");

        List<Order> list = new ArrayList<Order>();
        Order order = null;
        while (rs.next()) {
            order = new Order();
            order.setId(rs.getString("id"));
            order.setOrderNo(rs.getString("order_no"));
            list.add(order);
        }
        return list;
    }

    //开启事务，批量新增
    public void batchInsert() throws SQLException {
        conn.setAutoCommit(false);
        PreparedStatement pstmt = conn.prepareStatement("insert into order (order_no) values (?)");
        if (pstmt != null) {
            pstmt.clearParameters();
            pstmt.clearBatch();
        }
        for (int a = 1; a <= 10; a++) {
            String orderNo = "order_" + a;
            pstmt.setString(1, orderNo);
            pstmt.addBatch();
        }
        pstmt.executeBatch();
        conn.commit();
        conn.setAutoCommit(true);
    }

}
